package cn.zhp.mapper;

import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import java.util.Map;

public class WorkOrderSqlProvider {


    /**
     SELECT
     wo.*,
     cu.NAME create_user_name,
     cof.NAME create_office_name,
     tu.NAME transport_user_name,
     ru.NAME recipient_user_name
     FROM
     work_order wo
     LEFT JOIN sys_user cu ON wo.create_user_id = cu.id
     LEFT JOIN sys_user tu ON wo.transport_user_id = tu.id
     LEFT JOIN sys_user ru ON wo.recipient_user_id = ru.id
     LEFT JOIN sys_office cof ON cu.office_id = cof.id
     LEFT JOIN sys_office tof ON tu.office_id = tof.id
     LEFT JOIN sys_office rof ON ru.office_id = rof.id
     WHERE
     wo.del_flag = 0
     AND ( cof.id = 56 OR tof.id = 56 OR rof.id = 56 )
     AND wo.`status` = 1
     AND wo.create_date >= '2016-09-01'
     AND wo.create_date <= '2016-09-30'
     * 利用sql语句构建器创建动态sql
     * @param params
     * @return
     */
    public String selectPage(final Map<String, Object> params){
        return new SQL(){
            {
                SELECT("wo.*,cu.name create_user_name,cof.name create_office_name,tu.name transport_user_name,ru.name recipient_user_name");
                FROM("work_order wo");
                LEFT_OUTER_JOIN("sys_user cu on wo.create_user_id=cu.id");
                LEFT_OUTER_JOIN("sys_user tu on wo.transport_user_id=tu.id");
                LEFT_OUTER_JOIN("sys_user ru on wo.recipient_user_id = ru.id");
                LEFT_OUTER_JOIN("sys_office cof on cu.office_id=cof.id");
                LEFT_OUTER_JOIN("sys_office tof on tu.office_id=tof.id");
                LEFT_OUTER_JOIN("sys_office rof on ru.office_id=rof.id");
                WHERE("wo.del_flag=0");
                //{"startDate":xxx,endDate:xxx,officeId:xxx,status:xxx}
                if(params.containsKey("officeId")&& !StringUtils.isEmpty(params.get("officeId"))){
                    WHERE("(cof.id=#{officeId} or tof.id=#{officeId} or rof.id=#{officeId})");
                }
                if(params.containsKey("status")&& !StringUtils.isEmpty(params.get("status"))){
                    WHERE("wo.status=#{status}");
                }
                if(params.containsKey("startDate")&& !StringUtils.isEmpty(params.get("startDate"))){
                    WHERE("wo.create_date>=#{startDate}");
                }
                if(params.containsKey("endDate")&& !StringUtils.isEmpty(params.get("endDate"))){
                    WHERE("wo.create_date>=#{endDate}");
                }

            }
        }.toString();
    }
}
